It is desirable for database management systems (DBMSs) to support user-defined routines such as stored procedures and user-defined functions. Such user-defined routines are typically stored in libraries. A library is used by a database system when one of the routines contained in the library is invoked by an application accessing the database system. When this occurs, a library containing the stored user-defined routine is loaded from a long-term memory location to a working memory location from which the routine may be executed. Where the DBMS has a heavy workload that requires repeatedly invoking numerous routines located in numerous different libraries, the overhead in loading and unloading user-defined routine libraries can have a significant impact on the performance of the database system.
A DBMS may use a library-management strategy that keeps in the working memory all once-loaded user-defined routine libraries. This approach will avoid the overhead of repeatedly loading and unloading user-defined routine libraries but will require working memory to be dedicated to user-defined routine libraries that have been previously used but may no longer be actively used by the application. Another alternative solution is to impose a fixed limit on the number of user-defined routines that can be loaded into working memory at any one time. Typically, such a list is ordered from most recently used to least recently used. If the DBMS seeks to load a library after the maximum number of libraries is reached, the least recently used library is unloaded. This approach becomes less efficient, however, where there are many user-defined routine libraries that are consistently used by the DBMS. In this case, the number of libraries used may exceed the system limit and as a result libraries will frequently be loaded and unloaded. A system overhead associated with loading and unloading libraries may therefore potentially occur in such a system. In addition, such systems will require a system administrator to spend time to monitor and tune the configured size list.
It is therefore desirable to provide a database management system that supports user-defined routine libraries and which dynamically retains actively used libraries in memory while providing that user-defined routine libraries that are not actively used are unloaded.